package Model;

import java.io.UnsupportedEncodingException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;







import POJO.*;

public class CustomerModel {
	public static ArrayList<CustomerPOJO> DanhSachKhachHang() {
		Connection con = DataProvider.Connection();
		CustomerPOJO customer = null;
		ArrayList<CustomerPOJO> listCostomer = new ArrayList<CustomerPOJO>();
		if (con != null) {
			try {
				PreparedStatement ps;
				ps = con.prepareCall("select * from [customer]");
				ResultSet rs = ps.executeQuery();
				while (rs.next()) {
					customer = new CustomerPOJO();
					customer.SetID(rs.getInt("id"));
					customer.SetName(rs.getString("name"));
					customer.SetBir(rs.getString("birthday"));
					customer.SetADD(rs.getString("address"));
					customer.SetTel(rs.getString("tel"));
					customer.SetSex(rs.getString("sex"));
					listCostomer.add(customer);
				}
				con.close();
			} catch (SQLException ex) {
				return null;
			}
		}
		return listCostomer;
	}

	public static int AddCustomer(String name, String birthday, String Address,
			String Tel, String sex) throws UnsupportedEncodingException {

		Connection con = DataProvider.Connection();
		int flag = 0;
		if (con != null) {

			PreparedStatement ps;
			try {
				ps = con.prepareCall("Insert into [customer] Values(?,?,?,?,?)");
				byte pname[] = name.getBytes("ISO-8859-1"); 
	            String value1 = new String(pname, "UTF-8"); 
	            ps.setString(1,value1);
				
	            //
	            byte pbirth[]=birthday.getBytes("ISO-8859-1");
	            String value2= new String(pbirth,"UTF-8");
				ps.setString(2, value2);
				//
				byte padd[]=Address.getBytes("ISO-8859-1");
				String value3= new String(padd,"UTF-8");
				ps.setString(3, value3);
				//
				byte pTel[]=Tel.getBytes("ISO-8859-1");
				String value4= new String(pTel,"UTF-8");
				ps.setString(4, value4);
				//
				byte pSex[]=sex.getBytes("ISO-8859-1");
				String value5= new String(pSex,"UTF-8");
				ps.setString(5, value5);
				//
				int rs = ps.executeUpdate();
				if (rs == 1) {
					flag = 1;
				}

			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}

		}
		return flag;
	}
	public static ArrayList<CustomerPOJO> TimKiem(String Ser) throws UnsupportedEncodingException
	{
		Connection con = DataProvider.Connection();
		CustomerPOJO customer=null;
		ArrayList<CustomerPOJO> listCustomer= new  ArrayList<CustomerPOJO>();
		 {
	    	  try {
	                PreparedStatement ps;
	                if(Ser != "")
	                {
	               
	                ps  = con.prepareCall("select * from [customer] where name Like ? ");
	                byte pname[]=Ser.getBytes("ISO-8859-1");
	                String value1= new String(pname,"UTF-8");
	                value1 = '%' + value1 + '%';
	                ps.setString(1,value1);
	                }
	                else
	                {
	                	 ps  = con.prepareCall("select * from [customer] ");
	                }
	                ResultSet rs = ps.executeQuery();
	                while( rs.next()) {
	                	customer = new CustomerPOJO();
						customer.SetID(rs.getInt("id"));
						customer.SetName(rs.getString("name"));
						customer.SetBir(rs.getString("birthday"));
						customer.SetADD(rs.getString("address"));
						customer.SetTel(rs.getString("tel"));
						customer.SetSex(rs.getString("sex"));
						listCustomer.add(customer);
	                }
	                con.close();
	            } catch (SQLException ex) {
	                return null;
	            }    
	     }
	     return listCustomer;
	}
	public static int DeleteCustomer(String id) {
		Connection con = DataProvider.Connection();
		int flag = 0;
		if (con != null) {

			PreparedStatement ps;
		
			int id_order = 0;
			try {
				ps = con
						.prepareCall("select id from [order] where customer_id=?");
				ps.setString(1, id);
				ResultSet rs1 = ps.executeQuery();
				while (rs1.next()) {
					id_order = rs1.getInt("id");
					ps  = con
							.prepareCall("Delete from [item] where order_id=?");
					ps  .setInt(1, id_order);
					ps .executeUpdate();
				}
				ps = con
						.prepareCall("Delete from [order] where customer_id=?");
				ps .setString(1, id);
				ps .executeUpdate();

				ps = con.prepareCall("Delete from [customer] where id=?");
				ps.setString(1, id);
				int rs = ps.executeUpdate();
				if(rs==1)
				{
					flag=1;
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return flag;
	}
	public static CustomerPOJO selectCustomerByID (String id ) {
        Connection con = DataProvider.Connection();
    	CustomerPOJO customer = null;
        if (con != null){
            try {
                PreparedStatement ps;
                ps  = con.prepareCall("select * from [customer] where id=?");
                ps.setString(1, id);
                ResultSet rs = ps.executeQuery();
                if(rs.next()) {
                	customer = new CustomerPOJO();
					customer.SetID(rs.getInt("id"));
					customer.SetName(rs.getString("name"));
					customer.SetBir(rs.getString("birthday"));
					customer.SetADD(rs.getString("address"));
					customer.SetTel(rs.getString("tel"));
					customer.SetSex(rs.getString("sex"));
                	
                }
                con.close();
            } catch (SQLException ex) {
                return null;
            }    
        }
        return customer;
    }
	public static int UpdateCustomer(String name,String birth,String address,String ID,String tell,String sex) throws UnsupportedEncodingException
	{
		int flag=0;
		Connection con = DataProvider.Connection();
		if(con!=null)
		{
			  try {
	                PreparedStatement ps;
	                ps  = con.prepareCall("Update [customer] set name=?,birthday=?,address=?,tel=?,sex=? where id=?");
	                byte pname[] = name.getBytes("ISO-8859-1"); 
		            String value1 = new String(pname, "UTF-8"); 
		            ps.setString(1,value1);
					
		            //
		            byte pbirth[]=birth.getBytes("ISO-8859-1");
		            String value2= new String(pbirth,"UTF-8");
					ps.setString(2, value2);
					//
					byte padd[]=address.getBytes("ISO-8859-1");
					String value3= new String(padd,"UTF-8");
					ps.setString(3, value3);
					//
					byte pTel[]=tell.getBytes("ISO-8859-1");
					String value4= new String(pTel,"UTF-8");
					ps.setString(4, value4);
					//
					byte pSex[]=sex.getBytes("ISO-8859-1");
					String value5= new String(pSex,"UTF-8");
					ps.setString(5, value5);
					//
	                ps.setString(6,ID);
	                flag = ps.executeUpdate();              
	                con.close();
	            } catch (SQLException ex) {
	            	ex.printStackTrace();
	            }    
		}
		return flag;
	}
}
